# -*- coding:utf-8 -*-
import pandas as pd
from pandas import DataFrame
from urllib import request
from urllib import parse
import json
# import time
district_dict=dict()
"""
下载html
"""
def download_html(url):
    req = request.Request(url)
    req.add_header('User-Agent', 'Mozilla/6.0 (iPhone; CPU iPhone OS 8_0 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Version/8.0 Mobile/10A5376e Safari/8536.25')
    with request.urlopen(req) as f:
        data = f.read().decode("utf-8")
        return data
"""
地址转经纬度
"""
def address_to_location(address):
    key = address
    ress = "未知"
    if district_dict.get(key)==None:
        city = parse.quote("南宁")
        address = parse.quote(address)
        url = "https://restapi.amap.com/v3/geocode/geo?key=667dd97cc9ca502c88dee11bfdabf056&=city=%s&address=%s&output=JSON" % (city,address)
        res = download_html(url)
        print(res)
        data = json.loads(res)

        if data['status'] == '1':
            if len(data['geocodes'])!=0:
                a = data['geocodes'][0]['district']
                if isinstance(a,str):
                    ress = a
                    district_dict[key]=ress
    else:
        ress = district_dict.get(key)
    return ress

def trans(item):
    s = address_to_location(item)
    print("%s:%s" % (item,s))
    return s
# 读取文件
data = pd.read_excel("D:/mldong/日常工作/X-学生信息/5月学生审核名单.xls")

df = pd.DataFrame(data=data)

newDf = pd.DataFrame(df, columns=["学校名称"])
for i in range(len(newDf)):
    newDf.loc[i] = trans(newDf.loc[i,"学校名称"])
    # time.sleep(1)
df["城区"] = newDf
df['市民卡号']=[' %i' % i for i in df['市民卡号']]

df.to_excel("D:/mldong/日常工作/X-学生信息/5月学生审核名单2.xls",index = False)
print(district_dict)